﻿using ExchangeClient.Abstractions;
using ExchangeClient.Abstractions.Model;
using ExchangeClient.DbCache.Model;
using HKH.Data.Dapper;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using Org.BouncyCastle.Math.Field;
using System.Composition;
using System.Data;
using System.Reflection.Emit;
using System.Text.Json;
using System.Text.Json.Nodes;

namespace ExchangeClient.DbCache
{
    [Export(typeof(IExchangeClient))]
    [ExportMetadata(YdConstants.ID, "mysqlcache")]
    public class MySqlClient : DbExchangeClientBase<DbSetting>
    {
        public override string ChangePasswordWithError(string employeeNo, string password)
        {
            return base.ChangePasswordWithError(employeeNo, password);
        }
        public override AssetEvent? GetAssetEvent(string eventId)
        {
            return DataProvider.Query<AssetEvent>("SELECT * FROM [dbo].[AssetEvent] WHERE EventId = @EventId", new { EventId = eventId }).FirstOrDefault();
        }

        public override JsonObject GetData(JsonObject data)
        {
            string sql = "";
            if (data["Flag"].ToString() == "1")
            {
                sql = "SELECT * FROM `ContrastData`.`AssetContrast` ";
            }
            else if (data["Flag"].ToString() == "5")
            {
                sql = "SELECT SapAsset.* FROM SapAsset JOIN AssetMaster ON AssetMaster.BarCode = SapAsset.BarCode WHERE AssetMaster.FinancialCode != SapAsset.FinancialCode";
            }
            else if (data["Flag"].ToString() == "4")
            {

                string? deliverId = data["deliverId"]?.ToString();
                if (string.IsNullOrEmpty(deliverId))
                {
                    return new JsonObject
                    {
                        ["consequenceconsequence"] = -1,
                        ["error"] = "deliverId参数不能为空"
                    };
                }
                // 2. 使用COUNT(*)替代SELECT *，大幅提升性能
                string selectsql = "SELECT COUNT(*) FROM MiddlePush WHERE deliverId = @deliverId";
                // 3. 添加超时设置和参数化查询
                var count = DataProvider.Query<int>(
                    sql: selectsql,
                    param: new { deliverId = deliverId },
                    commandTimeout: 60  // 60秒超时
                ).FirstOrDefault();
                //var chechklist = DataProvider.Query<MiddlePush>("select * from MiddlePush where deliverId = @deliverId", new { deliverId = data["deliverId"].ToString() }).ToList();
                return new JsonObject
                {
                    ["consequenceconsequence"] = count,
                };
            }
            else if (data["Flag"].ToString() == "6")
            {

                // 1. 提取内部初始化的关键参数（deliverId 是核心去重标识）
                string? deliverId = data["deliverId"]?.ToString();
                string? barCode = data["BarCode"]?.ToString();
                string? opJsonData = data["DataJson"]?.ToString();
                string? pushState = data["PushSate"]?.ToString();
                string? dateTimeStr = data["Dateime"]?.ToString();
                string? fromName = data["FromNmae"]?.ToString();
                // 2. 先校验必传参数（避免空值导致插入异常）                                                
                if (string.IsNullOrEmpty(deliverId))
                {
                    Logger.Error($"deliverId={deliverId}，传入空值");
                    return new JsonObject
                    {
                        ["consequence"] = -1,
                        ["errMsg"] = $"deliverId={deliverId}，传入空值",
                    };
                }
                if (string.IsNullOrEmpty(barCode) || string.IsNullOrEmpty(opJsonData) || string.IsNullOrEmpty(pushState) || string.IsNullOrEmpty(dateTimeStr) || string.IsNullOrEmpty(fromName))
                {
                    Logger.Error("内部初始化错误：BarCode/DataJson/PushSate/Dateime/FromNmae不能为空");
                    return new JsonObject
                    {
                        ["consequence"] = -1,
                        ["errMsg"] = "内部初始化错误：BarCode/DataJson/PushSate/Dateime/FromNmae不能为空"
                    };
                }
                // 3. 核心：插入前强制查重
                string checkSql = "SELECT COUNT(*) FROM MiddlePush WHERE deliverId = @deliverId";
                var existCount = DataProvider.Query<int>(
                    sql: checkSql,
                    param: new { deliverId = deliverId },
                    commandTimeout: 60  // 60秒超时
                ).FirstOrDefault();

                // 4. 若已存在，直接返回错误（阻断插入）
                if (existCount > 0)
                {
                    Logger.Error($"内部流程重复：deliverId={deliverId}已存在，无需重复插入");
                    return new JsonObject
                    {
                        ["consequence"] = -1,
                        ["errMsg"] = $"内部流程重复：deliverId={deliverId}已存在，无需重复插入"
                    };

                }
                // 5. 若不存在，执行参数化插入
                var OpJsonData = data["DataJson"]!.ToString();
                //var OpJsonDatasql = "INSERT INTO MiddlePush (deliverId,BarCode,DataJson,Sate,PushSate,PushTime,UpdateTime,FromNmae) VALUES ('" + data["deliverId"].ToString() + "','" + data["BarCode"].ToString() + "','" + OpJsonData + "','正在进行','" + data["PushSate"].ToString() + "','" + data["Dateime"].ToString() + "','" + data["Dateime"].ToString() + "','" + data["FromNmae"].ToString() + "')";
                string insertSql = @"
                                INSERT INTO MiddlePush (
                                    deliverId, BarCode, DataJson, Sate, PushSate, PushTime, UpdateTime, FromNmae
                                ) VALUES (
                                    @deliverId, @barCode, @opJsonData, '正在进行', @pushState, @dateTime, @dateTime, @fromName
                                )";
                try
                {
                    int affectedRows = DataProvider.Execute(
                        sql: insertSql,
                        param: new
                        {
                            deliverId = deliverId,
                            barCode = barCode,
                            opJsonData = opJsonData,
                            pushState = pushState,
                            dateTime = dateTimeStr, // 确保格式与数据库datetime匹配（如'2024-05-20 15:00:00'）
                            fromName = fromName
                        },
                        commandTimeout: 60
                    );

                    return new JsonObject
                    {
                        ["consequence"] = 1,
                        ["message"] = "插入成功",
                        ["affectedRows"] = affectedRows
                    };
                }
                catch (MySqlException ex)
                {
                    Logger.Error($"插入失败：{ex.Message}");
                    // 兜底捕获其他MySQL错误（如极端并发下的幻读、字段类型不匹配等）
                    return new JsonObject
                    {
                        ["consequence"] = -3,
                        ["errMsg"] = $"插入失败：{ex.Message}",

                    };
                }
            }
            else
            {
                sql = "select * from SapAsset where BarCode  in (select BarCode from AssetMaster  where barcode!='')";
                //sql = "select * from SapAsset where BarCode='GE02010018'";
            }
            var assetData = DataProvider.Query<AssetMaster>(sql).ToList();
            string assetList = JsonSerializer.Serialize(assetData);
            var sapCount = DataProvider.Query<int>("select count(id)as c from SapAsset").First();
            var diffCount = DataProvider.Query<int>("select count(id)as c from AssetContrast").First();
            return new JsonObject
            {
                ["assetArray"] = assetList,
                ["sapCount"] = sapCount,
                ["diffCount"] = diffCount
            };
        }

        public override bool DeleteAsset(Asset asset)
        {
            int result = DataProvider.Execute("delete from SapAsset;delete from AssetMaster;delete from AssetContrast;");
            return result > 0;

        }
        public override JsonObject AddAsset(Asset asset)
        {
            Logger.Info("--------------已进入同步资产价值------------------");
            bool isComplete = false; int batchtotal = 0;
            var assets = asset.ExtraData["AssetMaster"].ToString();
            var assetList = JsonSerializer.Deserialize<List<AssetMaster>>(assets);
            if (assetList.Count > 0)
            {
                string batchNo = assetList[0].BatchId.ToString();
                batchtotal = assetList[0].BatchTotal ?? 0;
                BatchRecord record = DataProvider.Query<BatchRecord>("SELECT * FROM BatchRecord WHERE BatchNo = @BatchNo", new { BatchNo = batchNo }).FirstOrDefault();
                if (record == null)
                {
                    DataProvider.Execute("delete from SapAsset;delete from BatchRecord;delete from AssetContrast;INSERT into BatchRecord(batchNo,batchNum)VALUES('" + batchNo + "'," + batchtotal + ")");
                }
            }
            int flag = 0;
            string sql = "INSERT INTO `ContrastData`.`SapAsset` ( `FinancialCode`, `CompanyCode`, `OriginalValue`, `LJZJ`, `CapitalizationDate`, `ScrapDate`, `CenterCode`, `ZJDate`, `UseYear`, `UseMonth`, `ZJCode`, `BarCode`,`NumBeRaised`,`CostcenterName`,`IsDefer`) VALUES";
            string dataSql = "";
            Logger.Debug("sql语句拼接" + sql);
            Logger.Info("sql语句拼接" + sql);
            Logger.Debug("lalalal" + assetList);
            foreach (var item in assetList)
            {
                flag++;
                dataSql += "('" + item.FinancialCode + "','" + item.CompanyCode + "','" + item.OriginalValue + "','" + item.LJZJ + "','" + item.CapitalizationDate + "','" + item.ScrapDate + "','" + item.CenterCode + "','" + item.ZJDate + "','" + item.UseYear + "','" + item.UseMonth + "','" + item.ZJCode + "','" + item.BarCode + "','" + item.NumBeRaised + "','" + item.CostCenterName + "','" + item.IsDefer + "'),";
                Logger.Debug("sql数值的拼接" + dataSql);
                if (flag == 1000)
                {
                    dataSql = dataSql.Substring(0, dataSql.Length - 1) + ";";
                    Logger.Debug("最后生成的sql" + sql + dataSql);
                    Logger.Info("最后生成的sql" + sql + dataSql);
                    DataProvider.Execute(sql + dataSql);
                    dataSql = "";
                    flag = 0;
                }

            }
            if (flag > 0 && !string.IsNullOrEmpty(dataSql))
            {
                dataSql = dataSql.Substring(0, dataSql.Length - 1) + ";";
                DataProvider.Execute(sql + dataSql);
            }
            int total = DataProvider.Query<int>("SELECT count(*) FROM SapAsset ").FirstOrDefault();
            if (total == batchtotal)
            {

                AddNewCustomizedFormData(new CustomizedFormData());
                isComplete = true;
            }
            return new JsonObject
            {
                ["IsComplete"] = isComplete
            };
        }
        public override bool AddNewCustomizedFormData(CustomizedFormData form)
        {
            int result = DataProvider.Execute("delete from `ContrastData`.`AssetContrast`;INSERT INTO `ContrastData`.`AssetContrast` ( `FinancialCode`, `CompanyCode`, `OriginalValue`, `LJZJ`, `CapitalizationDate`, `ScrapDate`, `CenterCode`, `ZJDate`, `UseYear`, `UseMonth`, `ZJCode`, `BarCode`)\r\n(\r\nselect `FinancialCode`, `CompanyCode`, `OriginalValue`, `LJZJ`, `CapitalizationDate`, `ScrapDate`, `CenterCode`, `ZJDate`, `UseYear`, `UseMonth`, `ZJCode`, `BarCode` from AssetMaster where BarCode not in (select BarCode from SapAsset  where barcode!='')\r\n\r\nUNION \r\n\r\nselect `FinancialCode`, `CompanyCode`, `OriginalValue`, `LJZJ`, `CapitalizationDate`, `ScrapDate`, `CenterCode`, `ZJDate`, `UseYear`, `UseMonth`, `ZJCode`, `BarCode` from SapAsset where BarCode not in (select BarCode from AssetMaster where barcode!='' ))");
            return result > 0;
        }
        public override bool BatchAddAsset(List<Asset> assets)
        {
            var financialCodeField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "财务资产编码").First();
            var companyCodeField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "公司代码").First();
            var originalValueField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "资产原值").First();
            var lJZJField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "累计折旧").First();
            var capitalizationDateField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "本金化日期").First();
            var scrapDateField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "报废日期").First();
            var centerCodeField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "成本中心码").First();
            var zJDateField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "折旧开始日").First();
            var useYearField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "使用年").First();
            var useMonthField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "使用月").First();
            var zJCodeField = ExtendFields.Where(e => e.ObjectType == 0 && e.Name == "折旧码").First();
            int flag = 0;
            string sql = "INSERT INTO `ContrastData`.`AssetMaster` ( `FinancialCode`, `CompanyCode`, `OriginalValue`, `LJZJ`, `CapitalizationDate`, `ScrapDate`, `CenterCode`, `ZJDate`, `UseYear`, `UseMonth`, `ZJCode`, `BarCode`) VALUES";
            string dataSql = "";
            for (int i = 0; i < assets.Count; i++)
            {
                flag++;


                var asset = assets[i];
                string financialCode = asset.ExtensionFields.Where(e => e.Model == financialCodeField.FieldId).Select(e => e.Value).FirstOrDefault();
                string companyCode = asset.ExtensionFields.Where(e => e.Model == companyCodeField.FieldId).Select(e => e.Value).FirstOrDefault();
                string originalValue = asset.ExtensionFields.Where(e => e.Model == originalValueField.FieldId).Select(e => e.Value).FirstOrDefault();
                string lJZJ = asset.ExtensionFields.Where(e => e.Model == lJZJField.FieldId).Select(e => e.Value).FirstOrDefault();
                string capitalizationDate = asset.ExtensionFields.Where(e => e.Model == capitalizationDateField.FieldId).Select(e => e.Value).FirstOrDefault();
                string scrapDate = asset.ExtensionFields.Where(e => e.Model == scrapDateField.FieldId).Select(e => e.Value).FirstOrDefault();
                string centerCode = asset.ExtensionFields.Where(e => e.Model == centerCodeField.FieldId).Select(e => e.Value).FirstOrDefault();
                string zJDate = asset.ExtensionFields.Where(e => e.Model == zJDateField.FieldId).Select(e => e.Value).FirstOrDefault();
                string useYear = asset.ExtensionFields.Where(e => e.Model == useYearField.FieldId).Select(e => e.Value).FirstOrDefault();
                string useMonth = asset.ExtensionFields.Where(e => e.Model == useMonthField.FieldId).Select(e => e.Value).FirstOrDefault();
                string zJCode = asset.ExtensionFields.Where(e => e.Model == zJCodeField.FieldId).Select(e => e.Value).FirstOrDefault();
                dataSql += "('" + financialCode + "','" + companyCode + "','" + originalValue + "','" + lJZJ + "','" + capitalizationDate + "','" + scrapDate + "','" + centerCode + "','" + zJDate + "','" + useYear + "','" + useMonth + "','" + zJCode + "','" + asset.Barcode + "'),";
                if (flag == 1000)
                {
                    dataSql = dataSql.Substring(0, dataSql.Length - 1) + ";";
                    DataProvider.Execute(sql + dataSql);
                    dataSql = "";
                    flag = 0;
                }

            }
            if (flag > 0 && !string.IsNullOrEmpty(dataSql))
            {
                dataSql = dataSql.Substring(0, dataSql.Length - 1) + ";";
                DataProvider.Execute(sql + dataSql);
            }
            return true;
        }
        public override JsonObject SendNotifications(JsonObject data)
        {

            if (data["Type"]?.ToString() == "ClearScrapOrderSelect")
            {
                string selectSql = data["SelectSql"]!.ToString();
                if (!string.IsNullOrEmpty(selectSql))
                {
                    List<SapAssetScrapRequestView>? query = DataProvider.Query<SapAssetScrapRequestView>(selectSql).ToList();
                    if (query.Count == 0)
                    {
                        return new JsonObject
                        {
                            ["chechklist"] = false
                        };
                    }
                    else
                    {
                        return new JsonObject
                        {
                            ["chechklist"] = true,
                            ["data"] = JsonSerializer.Serialize(query).ToString()
                        };
                    }
                }

            }
            if (data["Type"]?.ToString() == "ClearScrapOrderUpdate")
            {
                string UpdateSql = data["UpdateSql"]!.ToString();
                if (!string.IsNullOrEmpty(UpdateSql))
                {
                    int result = DataProvider.Execute(UpdateSql);
                    if (result > 0)
                    {
                        return new JsonObject
                        {
                            ["chechklist"] = true
                        };
                    }
                    else
                    {
                        return new JsonObject
                        {
                            ["chechklist"] = false
                        };
                    }
                }
            }


            var chechklist = DataProvider.Query<SapAssetScrapRequestView>("SELECT * FROM SapAssetScrapRequest WHERE  IsTransferred=FALSE AND BarCode=@BarCode", new { BarCode = data["BarCode"].ToString() }).FirstOrDefault();
            if (chechklist != null)
            {
                return new JsonObject
                {

                    ["chechklist"] = false,
                    ["Message"] = $"已存在【{data["BarCode"].ToString()}】 这个资产编码啦，请勿重复推送",

                };
            }
            //不存在重复：将数据新增
            string insertSql = $"INSERT INTO SapAssetScrapRequest (id, FinancialCode,   CompanyCode, OriginalValue, LJZJ,  CapitalizationDate,  ScrapDate, CenterCode, ZJDate,UseYear,UseMonth,ZJCode,BarCode, ZSFZCSHQX, ZTSWC,BatchId,BatchTotal, IsTransferred,  CreateTime)  VALUES";
            try
            {

                insertSql += $" ('{Guid.NewGuid()}',{data["FinancialCode"].GetValue<long>()},'{data["CompanyCode"]?.ToString()}','{data["OriginalValue"]?.ToString()}','{data["LJZJ"]?.ToString()}','{data["CapitalizationDate"]?.ToString()}'," +
                    $" '{data["ScrapDate"]?.ToString()}','{data["CenterCode"]?.ToString()}','{data["ZJDate"].ToString()}','{data["UseYear"]?.ToString()}','{data["UseMonth"]?.ToString()}','{data["ZJCode"]?.ToString()}'," +
                    $"'{data["BarCode"]?.ToString()}','{data["ZSFZCSHQX"]?.ToString()}',{data["ZTSWC"]?.GetValue<int>()},'{data["BatchId"]?.ToString()}','{data["BatchTotal"]?.ToString()}',{false},'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}');";
                var resuflt = DataProvider.Execute(insertSql);
                if (resuflt == 0)
                {
                    return new JsonObject
                    {

                        ["chechklist"] = false,
                        ["Message"] = "入库失败"
                    };
                }
                return new JsonObject
                {
                    ["chechklist"] = true,
                    ["Message"] = "入库成功"
                };
            }
            catch (Exception ex)
            {

                throw new Exception($"SAP资产报废数据表--sql新增执行异常:{ex.Message}\n sql语句:{insertSql}\n数据{JsonSerializer.Serialize(data).ToString()}");
            }


        }



    }
}
